package com.housekeeping.housekeeping.administrator.adminsinformation.dao.impl;

import com.housekeeping.housekeeping.administrator.adminsinformation.dao.QeuryDao;
import com.housekeeping.housekeeping.administrator.entity.AdminsInformation;
import com.housekeeping.housekeeping.administrator.utils.Dbmanage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class QeuryDaoImpl implements QeuryDao {
    @Override
    public List<AdminsInformation> qeury(int offset, int limit,String station,String name) {
        List<AdminsInformation> list=new ArrayList<>();
        if ("".equals(name)&&!"".equals(station)) {
            return jurisdiction(offset,limit,station,list);
        }else if(!"".equals(name)&&!"".equals(station)) {
            return like(offset,limit,station,name,list);
        }else if ("".equals(name)&&"".equals(station)){
            return sum(offset,limit,list);
        }else {
            return amount(offset,limit,name,list);
        }
    }

    //总数据加搜索字查询
    public List<AdminsInformation> amount(int offset,int limit,String name,List list){

        String sql = "select *from admins_information where is_del=0 AND name LIKE "+"'"+"%"+name+"%"+"'"+"limit ? offset ?";
        return toolQeury(offset,limit,sql,list);
   }

    //总数据查询
    public List<AdminsInformation> sum(int offset, int limit,List list){

            String sql = "select *from admins_information where is_del=0 limit ? offset ?";
            return toolQeury(offset,limit,sql,list);
    }

    //权限查询
    public List<AdminsInformation> jurisdiction(int offset, int limit,String station,List list){

        String sql = "select *from admins_information where is_del=0 AND jurisdiction=" + "'" + station + "'" + " limit ? offset ?";
        return toolQeury(offset,limit,sql,list);
    }

    //搜索加权限查询
    public List<AdminsInformation> like(int offset, int limit,String station,String name,List list){

        String sql = "select *from admins_information where is_del=0 AND jurisdiction=" + "'" + station + "'" + " AND name LIKE "+ "'"+"%" + name + "%"+"'"+" limit ? offset ?";
        return toolQeury(offset,limit,sql,list);
    }

    //查询工具方法
    public List<AdminsInformation> toolQeury(int offset,int limit,String sql,List list){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        try {
            connection = Dbmanage.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setInt(1, limit);
            statement.setInt(2, offset);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                AdminsInformation adminsInformation = new AdminsInformation();
                adminsInformation.setId(resultSet.getInt(1));
                adminsInformation.setName(resultSet.getString(2));
                adminsInformation.setAccount(resultSet.getString(3));
                adminsInformation.setPassword(resultSet.getString(4));
                adminsInformation.setPhoneNumber(resultSet.getString(5));
                adminsInformation.setJurisdiction(resultSet.getString(6));
                adminsInformation.setRemark(resultSet.getString(7));
                adminsInformation.setDate(resultSet.getString(8));
                list.add(adminsInformation);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Dbmanage.close(connection, statement, resultSet);
        }
        return list;
    }
    @Override
    public int selectCount(String station,String name,int offset,int limit) throws SQLException {
        Connection connection = Dbmanage.getConnection();
//        String sql="";
//        if ("".equals(name)&&!"".equals(station)) {
//            sql = "select COUNT(*) from admins_information where is_del=0 AND jurisdiction=" + "'" + station + "'" + " limit ? offset ?";
//        }else if(!"".equals(name)&&!"".equals(station)) {
//            sql = "select COUNT(*) from admins_information where is_del=0 AND jurisdiction=" + "'" + station + "'" + " AND name LIKE "+ "'"+"%" + name + "%"+"'"+" limit ? offset ?";
//        }else if ("".equals(name)&&"".equals(station)){
//            sql = "select COUNT(*) from admins_information where is_del=0 limit ? offset ?";
//        }else {
//            sql = "select COUNT(*) from admins_information where is_del=0 AND name LIKE "+"'"+"%"+name+"%"+"'"+"limit ? offset ?";
//        }
        String sql = "SELECT COUNT(*)  FROM Admins_information WHERE is_del=0";
        PreparedStatement ps = connection.prepareStatement(sql);
//        ps.setInt(1,limit);
//        ps.setInt(2,offset);
        ResultSet rs = ps.executeQuery();

        int total = rs.next() ? rs.getInt(1) : 0;
        Dbmanage.close(ps,rs,connection);
        return total;
    }
}
